﻿-- =============================================
-- Script Template
-- =============================================
SELECT
  FIOFizLic._Fld5470 AS Familiyaaa
, FIOFizLic._Fld5471 AS Imyaaa
, FIOFizLic._Fld5472 AS Otchjestvo
, DolzhhhnostiOrganizaciy._Description AS dolzhnost
, PodrazdeleniyaaaOrganizaciy._Description AS Department
, GrafikiRabotj._Description AS Uslovie
, SotrudnikiOrganizaciy._Code AS TabNumber
, SotrudnikiOrganizaciy._IDRRef AS id
FROM [DB01].[Zar_kor_81].[dbo].[_InfoRg5080] AS RabotnikiOrganizaciy 
	INNER JOIN (SELECT DISTINCT MAX(_Period) AS Period, _Fld5081RRef AS Sotrudnik 
	FROM [DB01].[Zar_kor_81].[dbo].[_InfoRg5080] AS RabotnikiOrganizaciy 
		GROUP BY _Fld5081RRef) AS SotrudnikPeriod ON RabotnikiOrganizaciy._Period = SotrudnikPeriod.Period 
		AND RabotnikiOrganizaciy._Fld5081RRef = SotrudnikPeriod.Sotrudnik 
	LEFT OUTER JOIN [DB01].[Zar_kor_81].[dbo].[_Reference38] AS DolzhhhnostiOrganizaciy 
		ON RabotnikiOrganizaciy._Fld5088RRef = DolzhhhnostiOrganizaciy._IDRRef 
	LEFT OUTER JOIN [DB01].[Zar_kor_81].[dbo].[_Reference28 AS GrafikiRabotj] 
		ON RabotnikiOrganizaciy._Fld5089RRef = GrafikiRabotj._IDRRef 
	LEFT OUTER JOIN [DB01].[Zar_kor_81].[dbo].[_Reference67] AS PodrazdeleniyaaaOrganizaciy 
		ON RabotnikiOrganizaciy._Fld5087RRef = PodrazdeleniyaaaOrganizaciy._IDRRef 
	LEFT OUTER JOIN [DB01].[Zar_kor_81].[dbo].[_Reference84 AS SotrudnikiOrganizaciy] 
	LEFT OUTER JOIN (SELECT     MAX(_Period) AS Period, _Fld5469RRef AS FizLico 
		FROM [DB01].[Zar_kor_81].[dbo].[_InfoRg5468] AS FIOFizLic GROUP BY _Fld5469RRef) AS FIOMaks 
			INNER JOIN [DB01].[Zar_kor_81].[dbo].[_InfoRg5468] AS FIOFizLic 
				ON FIOMaks.Period = FIOFizLic._Period 
					AND FIOMaks.FizLico = FIOFizLic._Fld5469RRef 
			LEFT OUTER JOIN [DB01].[Zar_kor_81].[dbo].[_Reference101] AS FizichjeskieLica 
				ON FIOMaks.FizLico = FizichjeskieLica._IDRRef 
				ON SotrudnikiOrganizaciy._Fld993RRef = FIOMaks.FizLico 
				ON RabotnikiOrganizaciy._Fld5081RRef = SotrudnikiOrganizaciy._IDRRef 
			LEFT OUTER JOIN [DB01].[Zar_kor_81].[dbo].[_Enum285] AS PrichjinjIzmeneniyaaaSostoyaaaniyaaa 
				ON RabotnikiOrganizaciy._Fld5096RRef = PrichjinjIzmeneniyaaaSostoyaaaniyaaa._IDRRef
WHERE
	(PrichjinjIzmeneniyaaaSostoyaaaniyaaa._IDRRef <> 0xb33dfc9b1619f433433e45be20908286)
	ORDER BY Familiyaaa, Imyaaa, Otchjestvo


-- [DB01].[Zar_kor_81].[dbo].[



SELECT
	FIOFizLic._Fld5470 AS Familiyaaa
	, FIOFizLic._Fld5471 AS Imyaaa
	, FIOFizLic._Fld5472 AS Otchjestvo
	, DolzhhhnostiOrganizaciy._Description AS dolzhnost
	, PodrazdeleniyaaaOrganizaciy._Description AS Department
	, GrafikiRabotj._Description AS Uslovie
	, SotrudnikiOrganizaciy._Code AS TabNumber
	, SotrudnikiOrganizaciy._IDRRef AS id
FROM
	[DB01].[Zar_kor_81].[dbo].[_InfoRg5080] AS RabotnikiOrganizaciy 
		INNER JOIN (SELECT DISTINCT MAX(_Period) AS Period, _Fld5081RRef AS Sotrudnik
			FROM [DB01].[Zar_kor_81].[dbo].[_InfoRg5080] AS RabotnikiOrganizaciy
				GROUP BY _Fld5081RRef) AS SotrudnikPeriod 
					ON RabotnikiOrganizaciy._Period = SotrudnikPeriod.Period 
			AND RabotnikiOrganizaciy._Fld5081RRef = SotrudnikPeriod.Sotrudnik 
				LEFT OUTER JOIN
				[DB01].[Zar_kor_81].[dbo].[_Reference38] AS DolzhhhnostiOrganizaciy ON RabotnikiOrganizaciy._Fld5088RRef = DolzhhhnostiOrganizaciy._IDRRef LEFT OUTER JOIN
				[DB01].[Zar_kor_81].[dbo].[_Reference28] AS GrafikiRabotj ON RabotnikiOrganizaciy._Fld5089RRef = GrafikiRabotj._IDRRef LEFT OUTER JOIN
				[DB01].[Zar_kor_81].[dbo].[_Reference67] AS PodrazdeleniyaaaOrganizaciy ON RabotnikiOrganizaciy._Fld5087RRef = PodrazdeleniyaaaOrganizaciy._IDRRef LEFT OUTER JOIN
				[DB01].[Zar_kor_81].[dbo].[_Reference84] AS SotrudnikiOrganizaciy 
				LEFT OUTER JOIN
					(SELECT     MAX(_Period) AS Period, _Fld5469RRef AS FizLico
					FROM [DB01].[Zar_kor_81].[dbo].[_InfoRg5468] AS FIOFizLic
					GROUP BY _Fld5469RRef) AS FIOMaks 
						INNER JOIN [DB01].[Zar_kor_81].[dbo].[_InfoRg5468] AS FIOFizLic 
							ON FIOMaks.Period = FIOFizLic._Period AND FIOMaks.FizLico = FIOFizLic._Fld5469RRef 
						LEFT OUTER JOIN [DB01].[Zar_kor_81].[dbo].[_Reference101] AS FizichjeskieLica 
							ON FIOMaks.FizLico = FizichjeskieLica._IDRRef ON SotrudnikiOrganizaciy._Fld993RRef = FIOMaks.FizLico 
							ON RabotnikiOrganizaciy._Fld5081RRef = SotrudnikiOrganizaciy._IDRRef 
						LEFT OUTER JOIN [DB01].[Zar_kor_81].[dbo].[_Enum285] AS PrichjinjIzmeneniyaaaSostoyaaaniyaaa 
							ON RabotnikiOrganizaciy._Fld5096RRef = PrichjinjIzmeneniyaaaSostoyaaaniyaaa._IDRRef
WHERE
	(PrichjinjIzmeneniyaaaSostoyaaaniyaaa._IDRRef <> 0xb33dfc9b1619f433433e45be20908286)
ORDER BY Familiyaaa, Imyaaa, Otchjestvo


